﻿using QuanLyGiaoVuDAL.ADOWrapper;
using QuanLyGiaoVuDAL.DTO;
using QuanLyGiaoVuDAL.Mapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace QuanLyGiaoVuDAL.Repositories.DatabaseRepositories
{
    public class BanTinRepository:IBanTinRepository
    {
        public BanTinDTO GetBanTinById(Guid banTinID)
        {
            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = DBParameterFactory.Create("ID", banTinID);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_BanTin_All_Or_ByIdSelect]", DBParameterFactory.Create("@ID", banTinID));
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToBanTinDTO();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public IEnumerable<BanTinDTO> GetAllBanTin()
        {
            List<BanTinDTO> listBanTin = new List<BanTinDTO>();
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = DBParameterFactory.Create("ID", SqlDbType.UniqueIdentifier, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_BanTin_All_Or_ByIdSelect]",parameters);
            try
            {
                var caTable = dbResult.Tables[0].Rows;
                foreach (DataRow dataRow in caTable)
                {
                    listBanTin.Add(dataRow.ToBanTinDTO());
                }
                return listBanTin.AsEnumerable();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public IEnumerable<BanTinDTO> GetTopNumbersBanTin(int topNumber, int quyentruycap)
        {
            List<BanTinDTO> listBanTin = new List<BanTinDTO>();
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = DBParameterFactory.Create("TopNumber", topNumber);
            parameters[1] = DBParameterFactory.Create("QuyenTruyCap", quyentruycap);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_BanTin_TopNumberSelect]", parameters);
            try
            {
                var caTable = dbResult.Tables[0].Rows;
                foreach (DataRow dataRow in caTable)
                {
                    listBanTin.Add(dataRow.ToBanTinDTO());
                }
                return listBanTin.AsEnumerable();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public IEnumerable<BanTinDTO> GetTopNumbersBanTin2(int topNumber)
        {
            List<BanTinDTO> listBanTin = new List<BanTinDTO>();
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = DBParameterFactory.Create("TopNumber", topNumber);
            parameters[1] = DBParameterFactory.Create("QuyenTruyCap", -1);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_BanTin_TopNumberSelect]", parameters);
            try
            {
                var caTable = dbResult.Tables[0].Rows;
                foreach (DataRow dataRow in caTable)
                {
                    listBanTin.Add(dataRow.ToBanTinDTO());
                }
                return listBanTin.AsEnumerable();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public BanTinDTO InsertBanTin(BanTinDTO banTinDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_BanTinInsert]", GetParameterArray(banTinDTO));
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToBanTinDTO();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public BanTinDTO UpdateBanTin(BanTinDTO banTinDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_BanTinUpdate]", GetParameterArray(banTinDTO));
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToBanTinDTO();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public bool DeleteBanTin(Guid banTinID)
        {
            try
            {
                var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_BanTinDelete]", DBParameterFactory.Create("@ID", banTinID));
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        #region Private Methods
        private System.Data.SqlClient.SqlParameter[] GetParameterArray(BanTinDTO banTinDTO)
        {
            SqlParameter[] parameters = new SqlParameter[5];
            parameters[0] = DBParameterFactory.Create("ID", banTinDTO.ID);
            parameters[1] = DBParameterFactory.Create("TieuDe", banTinDTO.TieuDe);
            parameters[2] = DBParameterFactory.Create("NoiDung", banTinDTO.NoiDung);
            parameters[3] = DBParameterFactory.Create("NgayThem", banTinDTO.NgayThem);
            parameters[4] = DBParameterFactory.Create("QuyenTruyCap",banTinDTO.QuyenTruyCap);            
            return parameters;
        }
        #endregion
    }
}
